const conn = require('./connect').getConn();
const uuid = require('node-uuid');
const logger = require('../logger');
const filterKeys = {
  equal: '=',
  noEqual: '<>',
  greater: '>',
  less: '<',
  greaterEqual: '>=',
  lessEqual: '<=',
  between: 'between',
  like: 'like'
};

function query({sql, params = []}) {
  return new Promise((resolve) => {
    conn.query(sql, params, (err, result) => {
      if (err) {
        console.log('[SQL] ERROR] - ', err.message);
        logger.log('[SQL] ERROR] - ', err.message);
        resolve(-1);
        return;
      }
      resolve(result);
    });
  });
}

async function queryById({id, table}) {
  const sql = `select * from ${table} where id=?`;
  const result = await query({
    sql,
    params: [id]
  });
  if (result && result.length >= 0) {
    const data = result[0];
    delete data.unionid;
    delete data.openid;
    return data;
  } else if (result && result.length === 0) {
    return {};
  }
  return result;
}

async function deleteById({id, table}) {
  const sql = `delete from ${table} where id=?`;
  const result = await query({
    sql,
    params: [id]
  });
  return result;
}

async function updateById({id, table, data}) {
  const nowDate = new Date().getTime();
  const keySet = ['updateTime=?'];
  const valueSet = [nowDate];
  for (const key in data) {
    keySet.push(`${key}=?`);
    valueSet.push(data[key]);
  }
  const sql = `update ${table} set ${keySet.join(',')} where id='${id}'`;
  const result = await query({
    sql,
    params: valueSet
  });
  return result;
}

async function add({table, data}) {
  const nowDate = new Date().getTime();
  const keySet = ['id', 'inTime', 'updateTime'];
  const id = uuid.v1();
  const valueSet = [id, nowDate, nowDate];
  for (const key in data) {
    keySet.push(key);
    valueSet.push(data[key]);
  }
  const placeHolder = new Array(keySet.length + 1).join('?').split('');
  const sql = `insert into ${table}(${keySet.join(',')}) values(${placeHolder.join(',')})`;
  const result = await query({
    sql,
    params: valueSet
  });
  return {
    id,
    data: result
  };
}

/**
 * filter
 * {column: '', filterKey: '', value: ''}
 */
function buildFilters(filters = []) {
  let result = [];
  let params = [];
  for (let i = 0; i < filters.length; i++) {
    const filter = filters[i];
    const filterKey = filterKeys[filter.filterKey];
    if (filterKey) {
      result.push(`${filter.column} ${filterKey} ?`);
      params.push(filter.value);
    }
  }
  if (result.length > 0) {
    return {
      sql: ` where ${result.join(' and ')}`,
      params
    };
  }
  return {
    sql: '',
    params
  };
}

async function list({
  table,
  columns,
  filters,
  pageNum = 1,
  pageSize = 10,
  orderBy = [{column: 'inTime', type: 'desc'}]
}) {
  // 过滤掉 unioinid 和 openid，这两个不允许查询
  columns = columns.filter((item) => {
    return item !== 'unionid' && item !== 'openid';
  });
  let sql = `select ${columns.join(',')} from ${table}`;
  const filterObj = buildFilters(filters);
  sql = `${sql}${filterObj.sql}`;
  const orderByAttr = [];
  for (let i = 0; i < orderBy.length; i++) {
    orderByAttr.push(`${orderBy[i].column} ${orderBy[i].type}`);
  }
  sql = `${sql} order by ${orderByAttr.join(',')}`;
  const limitStart = (pageNum - 1) * pageSize;
  sql = `${sql} limit ${limitStart}, ${pageSize}`;
  const result = await query({sql, params: filterObj.params});

  // 查询总数
  const countResult = await query({
    sql: `select count(1) total from ${table} ${filterObj.sql}`,
    params: filterObj.params
  });

  return {
    list: result,
    total: countResult[0].total
  };
}

module.exports = {
  query,
  deleteById,
  updateById,
  add,
  list,
  queryById
};
